# coding: utf-8
# 将指标树电子表格转换成插入Oracle的SQL语句
#
# 脚本运行依赖 pandas 和 numpy 第三方包，用于读取电子表格这类文件格式，其他无依赖
# 安装依赖的方式，在安装有python环境下 pip install pandas numpy
#
# 运行方式：
# python gen.py <PC条件选股指标树.xlsx> <result.sql>
import logging
import sys

import numpy as np
import pandas as pd

from datetime import datetime, timezone


def is_empty_or_nan(value):
    if value is None:
        return True
    if isinstance(value, str):
        return False if value.strip() else True
    if isinstance(value, np.float) and np.isnan(value):
        return True
    return False


def is_ready(record):
    if record.IS_LEAF == '0':
        if is_empty_or_nan(record.VERSION_ID):
            return False
        return True
    return not is_empty_or_nan(record.SERVICE_NAME)


def convert_nan_to_null(value):
    if isinstance(value, np.float) and np.isnan(value):
        return 'null'
    else:
        return "'%s'" % value


def gen_sql(excel, result_file):
    logging.info(f"Processing from {excel} to {result_file}")
    old_output = sys.stdout
    sys.stdout = open(result_file, mode="w", encoding='utf-8')

    print(f"-- Generated by Gen/SQL programme on {datetime.now(tz=timezone.utc)} --")
    print("-- 码表数据生成器  v 1.0")

    df = pd.read_excel(excel, sheet_name='指标树', dtype={
        "NODE": str,
        "NODE_NAME": str,
        "PARENTS": str,
        "IS_LEAF": str,
        "DATA_TYPE": str,
        "INDICATORS": str,
        "SERVICE_NAME": str,
        "FUNCTION_NAME": str,
        "PARAMETER": str,
        "EXTRA_PARAMETER": str,
        "UNIT": str,
        "PRECISIONS": str,
        "HELP_NOTES": str,
        "VERSION_ID": str
    })
    count = 0
    for i, record in df.iterrows():
        if not is_ready(record):
            continue
        sql = f"""
INSERT INTO RDMDM.ZX_PCXG_NODE (NODE, NODE_NAME, PARENTS, IS_LEAF, DATA_TYPE, INDICATORS, SERVICE_NAME, 
            FUNCTION_NAME, PARAMETER, EXTRA_PARAMETER, UNIT, PRECISIONS, HELP_NOTES, VERSION_ID, UPDATE_TIME)
VALUES ('{record.NODE}', '{record.NODE_NAME}', {convert_nan_to_null(record.PARENTS)}, {record.IS_LEAF},
    {convert_nan_to_null(record.DATA_TYPE)}, {convert_nan_to_null(record.INDICATORS)}, 
    {convert_nan_to_null(record.SERVICE_NAME)}, {convert_nan_to_null(record.FUNCTION_NAME)}, 
    {convert_nan_to_null(record.PARAMETER)}, {convert_nan_to_null(record.EXTRA_PARAMETER)},
    {convert_nan_to_null(record.UNIT)}, {convert_nan_to_null(record.PRECISIONS)},
    {convert_nan_to_null(record.HELP_NOTES)}, {convert_nan_to_null(record.VERSION_ID)}, sysdate);
         """
        print(sql)
        count += 1
    sys.stdout.close()
    sys.stdout = old_output
    print(f"Done with {count} records.", file=sys.stderr)


if __name__ == "__main__":
    gen_sql(sys.argv[1], sys.argv[2])
